﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Linq;
using System.Text;
using JKF.BusinessLayer.BLL;
using JKF.DataModule;
using JKF.DataModule.SqlServerDAL;
using JKF.Entities.Model;
using JKF.Substructure;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
//using System.Data.Common;
namespace JKF.BusinessLayer.BLL
{
    public partial class bllSWING_CARD
    {
        public DataTable GetSwingCard(string searchItem, string matchWay, string keyword, string dateStart, string dateEnd,
           string ownShop,int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            dalSWING_CARD dalSwingCard = new dalSWING_CARD();
            return dalSwingCard.GetSwingCard(searchItem, matchWay, keyword, dateStart, dateEnd,
          ownShop, bllSYS_USER.CurrentUser.USER_KEY.ToString(), pageCurrent, pageSize, ref pageCount, ref rowCount);
        }

        public bool DeleteSwingCard(string id)
        {
            var dbClass = DB.HR;
            var cmdList = new List<DbCommand>();

            try
            {
                //根据体验记录得到顾客key
                var strSql = "SELECT CUSTOMER_KEY FROM  SWING_CARD WHERE ID=@ID";
                var cmd = dbClass.GetSqlStringCommand(strSql);
                dbClass.AddInParameter(cmd, "ID", DbType.Guid, new Guid(id));
                object customerKey = dbClass.ExecuteScalar(cmd);

                string logContent = "";
                DataRow tmpRow = bllPOINT_EXCHANGE.GetCustomerByKey(customerKey.ToString());
                if (tmpRow != null)
                {
                    logContent = string.Format("删除体验记录，顾客姓名\"{0}\"  手机号\"{1}\":", tmpRow["NAME"].ToString(), tmpRow["MOBILE"].ToString());
                    bllSYS_LOG.SaveLog("003002001", "SWING_CARD", id, logContent, "删除", BusinessBase.ClientIp);
                }

                //得到体验积分
                var strSql_Point = "select TOP 1 POINT_VALUE"
                + "  from dbo.SWING_CARD A "
                + " INNER JOIN dbo.POINT_LOG B ON A.POINT_KEY=B.POINT_KEY "
                + " WHERE A.ID=@ID";
                var cmd_point = dbClass.GetSqlStringCommand(strSql_Point);
                dbClass.AddInParameter(cmd_point, "ID", DbType.Guid, new Guid(id));
                object pointValue = dbClass.ExecuteScalar(cmd_point);
                if (string.IsNullOrEmpty(pointValue.ToString()))
                    pointValue = 0;


                //退回体验积分
                string sql_recover_point = "UPDATE CUSTOMER  "
                  + " SET GIVE_POINTS=GIVE_POINTS+" + Int32.Parse(pointValue.ToString())
                  + " WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
                var recover_point_cmd = dbClass.GetSqlStringCommand(sql_recover_point);
                dbClass.AddInParameter(recover_point_cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey.ToString()));
                cmdList.Add(recover_point_cmd);

                //更改状态
                const string sql_SWING_CARD_update = "UPDATE  SWING_CARD SET STATUS='9',DATE_UPDATE=GETDATE() WHERE ID=@ID";
                var WING_CARD_update_Cmd = dbClass.GetSqlStringCommand(sql_SWING_CARD_update);
                dbClass.AddInParameter(WING_CARD_update_Cmd, "ID", DbType.Guid, new Guid(id));
                cmdList.Add(WING_CARD_update_Cmd);


                ExecTransaction.Exec(cmdList);
                return true;
            }
            catch
            {
                return false;
            }

        }

        public string GetTotalPoint(string customerKey)
        {
            var dbClass = DB.HR;

            var strSql = "SELECT SUM(POINT_VALUE) as TOTAL_POINT"
            + " FROM  dbo.POINT_LOG  "
            + " WHERE  CUSTOMER_KEY=@CUSTOMER_KEY"
           + " GROUP BY CUSTOMER_KEY";
            var cmd = dbClass.GetSqlStringCommand(strSql);
            dbClass.AddInParameter(cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey));
            object POINT_VALUE = dbClass.ExecuteScalar(cmd);
            return POINT_VALUE.ToString();
        }

        /// <summary>
        /// 获得顾客的卡号变更日志
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        public static DataTable GetCustomerCardChangeLog(string customerKey, string currentUser)
        {
            return dalSWING_CARD.GetCustomerCardChangeLog(customerKey, currentUser);
        }

        public bool IsHasSwingCardRecord(string customerKey)
        {
            var dbClass = DB.HR;

            var strSql = "SELECT ID"
            + " FROM  SWING_CARD  "
            + " WHERE  CUSTOMER_KEY=@CUSTOMER_KEY AND CONVERT(CHAR(10),TRY_TIME,120)=CONVERT(CHAR(10),GETDATE(),120)";
            var cmd = dbClass.GetSqlStringCommand(strSql);
            dbClass.AddInParameter(cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey));
            object result = dbClass.ExecuteScalar(cmd);
            if(result!=null)
                return true;
            else
                return false;
        }

        public DataSet GetSwingSequenceByShopCode(string shopCode,string try_date)
        {
            if (shopCode == "")
                return null;

            if (string.IsNullOrEmpty(try_date))
                try_date = DateTime.Now.ToShortDateString();

            try
            {
                DateTime.Parse(try_date);
            }
            catch
            {
                try_date = DateTime.Now.ToShortDateString();
            }

            var strSql = @"select a.customer_key,b.CARD_NO,b.NAME,a.TRY_TIME
                from dbo.SWING_CARD a
                inner join dbo.CUSTOMER b on a.customer_key=b.customer_key
                where  a.shop_code=@shop_code  
                and convert(char(10),a.try_time,120)=@try_time
                order by try_time asc";
            var dbClass = DB.HR;
            var cmd = dbClass.GetSqlStringCommand(strSql);
            dbClass.AddInParameter(cmd, "shop_code", DbType.String, shopCode);
            dbClass.AddInParameter(cmd, "try_time", DbType.String,try_date);
            var ds = dbClass.ExecuteDataSet(cmd);
            return ds;
        }

        /// <summary>
        /// 添加体验记录
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="tryType"></param>
        /// <returns></returns>
        public string SaveSwingCard(string customerKey, string tryType)
        {
            var dalPointLog = new dalPOINT_LOG();
            var dalSwingCard = new dalSWING_CARD();
            POINT_LOGinfo pointLogInfo = new POINT_LOGinfo();

            bllSYS_PARAM_VALUE paramValue = new bllSYS_PARAM_VALUE();
            string pointValue= paramValue.GetParamValueByKey("EXPERIENCE_POINTS"); //每次体验积分值
            pointValue = pointValue == "" ? "1" : pointValue;
            var cmdList = new List<DbCommand>();
            var pointKey = DB.GetNewGuid(DB.HR);
            pointLogInfo.POINT_KEY = new Guid(pointKey);
            pointLogInfo.CUSTOMER_KEY = new Guid(customerKey);
            pointLogInfo.CODE = "02";
            pointLogInfo.WAY = "体验刷卡";
            pointLogInfo.POINT_TIME = DateTime.Now;
            pointLogInfo.POINT_VALUE =Int32.Parse(pointValue);
            pointLogInfo.STATUS = "1";
            pointLogInfo.SHOP_CODE = bllSYS_USER.CurrentUser.SHOP_CODE;
            pointLogInfo.USER_CREATE = bllSYS_USER.CurrentUser.USER_KEY;
            cmdList.Add(dalPointLog.GetAddDbCommand(pointLogInfo));

            var dbClass = DB.HR;
            var pk = DB.GetNewGuid(DB.HR);
            SWING_CARDinfo swingCardInfo = new SWING_CARDinfo();
            swingCardInfo.ID = new Guid(pk);
            swingCardInfo.POINT_KEY = new Guid(pointKey);
            swingCardInfo.CUSTOMER_KEY = new Guid(customerKey);
            swingCardInfo.TRY_TIME = DateTime.Now;
            swingCardInfo.TYPE = tryType;
            swingCardInfo.SHOP_CODE = bllSYS_USER.CurrentUser.SHOP_CODE;
            swingCardInfo.STATUS = "1";
            swingCardInfo.USER_CREATE = bllSYS_USER.CurrentUser.USER_KEY;
            swingCardInfo.DATE_CREATE = DateTime.Now;
            cmdList.Add(dalSwingCard.GetAddDbCommand(swingCardInfo));


            string sql_add_point = "UPDATE CUSTOMER  "
            +"SET GIVE_POINTS=GIVE_POINTS+" + pointValue + " WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
            var add_point_cmd = dbClass.GetSqlStringCommand(sql_add_point);
            dbClass.AddInParameter(add_point_cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey));
            cmdList.Add(add_point_cmd);
            ExecTransaction.Exec(cmdList);

            return pk;
        }

        public DataSet GetRemindInfo(string remindType)
        {
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("proc_sms_remind");
            db.AddInParameter(dbCommand, "@remind_type", DbType.String, remindType);
            db.AddInParameter(dbCommand, "@user_create", DbType.String, bllSYS_USER.CurrentUser.USER_KEY.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds;
        }

        public DataSet ImportCustomer(string sqlText)
        {
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("proc_customer_import");
            db.AddInParameter(dbCommand, "@shop_code", DbType.String, bllSYS_USER.CurrentUser.SHOP_CODE);
            db.AddInParameter(dbCommand, "@user_create", DbType.String, bllSYS_USER.CurrentUser.USER_KEY.ToString());
            db.AddInParameter(dbCommand, "@sqlText", DbType.String, sqlText);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds;
        }
   
    }
}
